{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZEIhmQhJvgie"
   },
   "source": [
    "# Install Dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "xqrFsf5nvmB_"
   },
   "outputs": [],
   "source": [
    "!pip install numpy==1.21.4\n",
    "!pip install pandas==1.3.4\n",
    "!pip install loguru==0.5.3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ipX-FF3avuts"
   },
   "source": [
    "# Mount Clone Extract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "p49PnuUGt-HM"
   },
   "source": [
    "**In case you have enough space on your disk, you could download full and updated database using `wget`, extract the database files and enjoy. Keep in mind, archived texts are prone to expand ~10x in size and if your archive is just 30 Gb, it's probably gonna take another 300 Gb after extraction. In any case, there's a nice small but sufficient chunk of data (November 2021) in the cloned repo.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tVd6ywt5rix0"
   },
   "outputs": [],
   "source": [
    "from google.colab import drive\n",
    "drive.mount(\"/content/drive\", force_remount=True)\n",
    "# replace with your path\n",
    "%cd \"drive/MyDrive/gar_colab\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Zcm8fEnfq8xi"
   },
   "outputs": [],
   "source": [
    "!git clone https://github.com/nurtdinovadf/garbdfias.git"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6cy5G9a8rEHO"
   },
   "outputs": [],
   "source": [
    "%cd garbdfias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "lq9VxcMyuWRf"
   },
   "outputs": [],
   "source": [
    "!ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7jter17duXh3"
   },
   "outputs": [],
   "source": [
    "!tar -xvf data.tar.gz"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "vdwkbIJ-ubUR"
   },
   "outputs": [],
   "source": [
    "!ls data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kvLIalZPwJdk"
   },
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:15.686816Z",
     "start_time": "2021-12-10T12:09:15.397694Z"
    },
    "id": "VYyy6Cb3ulm2"
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "import gc\n",
    "import glob\n",
    "import os\n",
    "import time\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from loguru import logger\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "LYGztaFZwRw4"
   },
   "source": [
    "# Define Helper Functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "zT0cd0Fqt-HS"
   },
   "source": [
    "**These are the utilities I'm gonna use, it's just nice to have them all gathered in one place. I provided some short descriptions, and I might or might not explain what's going on a bit later.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:32:56.833332Z",
     "start_time": "2021-12-10T12:32:56.814684Z"
    },
    "id": "hm21ALATwO6Q"
   },
   "outputs": [],
   "source": [
    "def cleanup(x):\n",
    "    \"\"\"\n",
    "    Manual object cleaning\n",
    "    \"\"\"\n",
    "    del x\n",
    "    gc.collect()\n",
    "\n",
    "\n",
    "def parse_xml(x):\n",
    "    \"\"\"\n",
    "    Parse GAR XML file into pandas dataframe object\n",
    "    \"\"\"\n",
    "    tree = ET.parse(x)\n",
    "    root = tree.getroot()\n",
    "    df = [child.attrib for child in root]\n",
    "    df = pd.DataFrame.from_dict(df)\n",
    "    return df\n",
    "\n",
    "\n",
    "def get_adms(df):\n",
    "    \"\"\"\n",
    "    Get administrative \"object-parent\" relations into dictionary for later use\n",
    "    \"\"\"\n",
    "    rftree = df[['OBJECTID', 'PARENTOBJID']].groupby(\n",
    "        by='OBJECTID'\n",
    "    )['PARENTOBJID'].apply(list).to_dict()\n",
    "    return rftree\n",
    "\n",
    "\n",
    "def get_adms_rec_rev(chain, rdadm, housdict, objdict):\n",
    "    \"\"\"\n",
    "    Recursive address chain builder\n",
    "    \"\"\"\n",
    "    objid = chain[-1]\n",
    "\n",
    "    if objid in rdadm and objid == objid:\n",
    "        prnts = rdadm[objid]\n",
    "        if len(prnts) > 1:\n",
    "            prnts = [x for x in prnts if x in objdict]\n",
    "            if len(prnts) > 1:\n",
    "                seedOKTMO = housdict[chain[0]]['OKTMO']\n",
    "                prntOKTMO = {prnt: objdict[prnt]['OKTMO'] for prnt in prnts}\n",
    "                prnts = [prnt for prnt in prnts if len(set(seedOKTMO).intersection(set(prntOKTMO[prnt]))) > 0]\n",
    "            if len(prnts) == 0:\n",
    "                return None\n",
    "        chains = [chain + [obj] for obj in prnts if obj == obj]\n",
    "        if len(chains) > 1:\n",
    "            return [get_adms_rec_rev(ch, rdadm, housdict, objdict) for ch in chains]\n",
    "        if len(chains) == 0:\n",
    "            return chain\n",
    "        return get_adms_rec_rev(chains[0], rdadm, housdict, objdict)\n",
    "    else:\n",
    "        return chain\n",
    "\n",
    "    \n",
    "def reduce_included(x):\n",
    "    \"\"\"\n",
    "    Reduce included chains\n",
    "    \"\"\"\n",
    "    maxl = 0\n",
    "    maxch = []\n",
    "    x = [tuple(y) for y in x]\n",
    "    for y in x:\n",
    "        maxl = (len(y) > maxl) * len(y) + (len(y) <= maxl) * maxl\n",
    "        maxch = y if len(y) == maxl else maxch\n",
    "    mask = [len(set(y).intersection(set(maxch))) == len(y) and y != maxch for y in x]\n",
    "    ret = [y for y, z in zip(x, mask) if not z]\n",
    "    single = False\n",
    "    if len(ret) == 1:\n",
    "        ret = ret[0]\n",
    "        single = True\n",
    "    return ret, single\n",
    "\n",
    "\n",
    "def get_town(x):\n",
    "    \"\"\"\n",
    "    Chain post-cleanup.\n",
    "    \"\"\"\n",
    "    priority = ['5', '6', '4', '7', '1']\n",
    "    street = [f'{i}' for i in range(8, 0, -1)]\n",
    "    streets = [p for p in street if x[p] == 1]\n",
    "    if len(streets) == 0:\n",
    "        street = None\n",
    "    else:\n",
    "        street = streets[0]\n",
    "    town = [p for p in priority if p != street and x[p] == 1]\n",
    "    town = town[0] if len(town) > 0 else None\n",
    "    leftover = [\n",
    "        x for x in streets\n",
    "        if x != street\n",
    "        and x != town\n",
    "        and x not in ['1', '2', '3']\n",
    "    ]\n",
    "    muni = [x for x in streets if x in ['2', '3']]\n",
    "\n",
    "    return street, town, leftover, muni"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "laAYHK87xAQB"
   },
   "source": [
    "# Core"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dvy2nYLwxjrU"
   },
   "source": [
    "## Define Region"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "i466D5M4t-HU"
   },
   "source": [
    "**We'll be working on one particular region in this tutorial.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:22.435554Z",
     "start_time": "2021-12-10T12:09:22.419065Z"
    },
    "id": "22ZKvqL5w7Ba"
   },
   "outputs": [],
   "source": [
    "regions = glob.glob('data/[0-9][0-9]')\n",
    "region = regions[0]\n",
    "region"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "WuYzxicYxc14"
   },
   "source": [
    "## Read Address Object File"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "s0eYPY_Et-HW"
   },
   "source": [
    "**XML files in the DB are quite similar in structure, so we'll use `parse_xml` utility to convert XML files to pandas DataFrames.**\n",
    "\n",
    "**Filter out historical data by `ISACTUAL` and `ISACTIVE` attributes.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:23.909590Z",
     "start_time": "2021-12-10T12:09:23.690736Z"
    },
    "id": "K4-4guZbxHOB"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob(os.path.join(region, 'AS_ADDR_OBJ_*.XML'))\n",
    "fname = [x for x in fname if 'PARAMS' not in x and 'DIVISION' not in x]\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "adobj = parse_xml(fname)\n",
    "adobj = adobj[(adobj['ISACTUAL'] == '1') & (adobj['ISACTIVE'] == '1')]\n",
    "adobj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RUjJh2C3t-HW"
   },
   "source": [
    "**The way of joining address object types is a bit messed up, just like a lot of other stuff in here. We'll rename some columns right away as to not to string along same info in different columns.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:24.257081Z",
     "start_time": "2021-12-10T12:09:24.224744Z"
    },
    "id": "bUshQ7SHxrtm"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob('data/AS_ADDR_OBJ_TYPES_*.XML')\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "adobjt = parse_xml(fname)\n",
    "adobjt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:24.844422Z",
     "start_time": "2021-12-10T12:09:24.787572Z"
    },
    "id": "VQ1n_m60zfy2"
   },
   "outputs": [],
   "source": [
    "adobj = adobj.merge(\n",
    "    adobjt[['SHORTNAME', 'DESC', 'LEVEL']].rename(\n",
    "        columns={\n",
    "            'SHORTNAME': 'TYPENAME',\n",
    "            'DESC': 'TYPELONGNAME'\n",
    "        }\n",
    "    ),\n",
    "    on=['LEVEL', 'TYPENAME']\n",
    ")\n",
    "cleanup(adobjt)\n",
    "adobj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YWqQF_d5t-HX"
   },
   "source": [
    "**Levels are not as strict as one might think and similar address objects ofthen end up on different levels.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:25.628540Z",
     "start_time": "2021-12-10T12:09:25.585338Z"
    },
    "id": "gDzd_kGwxu6T"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob('data/AS_OBJECT_LEVELS_*.XML')\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "lev = parse_xml(fname)\n",
    "\n",
    "adobj = adobj.merge(\n",
    "    lev[['NAME', 'LEVEL']].rename(\n",
    "        columns={\n",
    "            'NAME': 'LEVELNAME'\n",
    "        }\n",
    "    ),\n",
    "    on='LEVEL'\n",
    ")\n",
    "adobj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YfMuh6iAt-HX"
   },
   "source": [
    "**Some additional parameters, we'll need only `OKTMO` attribute, which is type 7 btw.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:27.722095Z",
     "start_time": "2021-12-10T12:09:26.396649Z"
    },
    "id": "zMtoB1GLt-HX"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob(os.path.join(region, 'AS_ADDR_OBJ_PARAMS_*.XML'))\n",
    "fname = fname[0]\n",
    "adobjp = parse_xml(fname)\n",
    "adobjp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:28.955738Z",
     "start_time": "2021-12-10T12:09:27.723616Z"
    },
    "id": "YjgUEhK8t-HX"
   },
   "outputs": [],
   "source": [
    "adobjp = adobjp[\n",
    "    (\n",
    "        adobjp['TYPEID'] == '7'\n",
    "    ) & (\n",
    "        adobjp['CHANGEIDEND'] == '0'\n",
    "    )\n",
    "]\n",
    "adobjp = adobjp[\n",
    "    adobjp.ENDDATE.apply(\n",
    "        lambda x: datetime.datetime.strptime(\n",
    "            x, '%Y-%m-%d'\n",
    "        ) > datetime.datetime.fromtimestamp(\n",
    "            time.time()\n",
    "        )\n",
    "    )\n",
    "]\n",
    "adobjp = adobjp[['OBJECTID', 'VALUE']].rename(\n",
    "    columns={'VALUE': 'OKTMO'}\n",
    ").drop_duplicates().groupby(\n",
    "    by='OBJECTID'\n",
    ").agg(\n",
    "    lambda x: x.to_list()\n",
    ").to_dict('index')\n",
    "adobjp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pRAxo_2wyPSh"
   },
   "source": [
    "## Read Houses File"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "grM7gQGkt-HY"
   },
   "source": [
    "**Reading houses data process is more or less the same.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:35.571154Z",
     "start_time": "2021-12-10T12:09:29.001525Z"
    },
    "id": "H08LaQMkyI8B"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob(os.path.join(region, 'AS_HOUSES_*.XML'))\n",
    "fname = [x for x in fname if 'PARAMS' not in x]\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "hous = parse_xml(fname)\n",
    "hous = hous.rename(\n",
    "    columns={\n",
    "        'ADDTYPE1': 'HOUSETYPE1',\n",
    "        'ADDTYPE2': 'HOUSETYPE2',\n",
    "        'ADDNUM1': 'HOUSENUM1',\n",
    "        'ADDNUM2': 'HOUSENUM2'\n",
    "    }\n",
    ")\n",
    "if 'ISACTUAL' in hous.columns:\n",
    "    hous = hous[(hous['ISACTUAL'] == '1') & (hous['ISACTIVE'] == '1')]\n",
    "else:\n",
    "    hous = hous[(hous['ISACTIVE'] == '1')]\n",
    "hous.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:35.601981Z",
     "start_time": "2021-12-10T12:09:35.572734Z"
    },
    "id": "2lBV-l7nyXgv"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob('data/AS_HOUSE_TYPES_*.XML')\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "houst = parse_xml(fname)\n",
    "houst = houst.rename(\n",
    "    columns={\n",
    "        'SHORTNAME': 'TYPENAME',\n",
    "        'DESC': 'TYPELONGNAME',\n",
    "        'ID': 'HOUSETYPE'\n",
    "    }\n",
    ")\n",
    "houst.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dTnwb6ntt-HZ"
   },
   "source": [
    "**Here's the main difference: two additional columns to store buildings.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:35.626539Z",
     "start_time": "2021-12-10T12:09:35.603507Z"
    },
    "id": "KsnZKRPct-HZ"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob('data/AS_ADDHOUSE_TYPES_*.XML')\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "housta = parse_xml(fname)\n",
    "housta = housta.rename(\n",
    "    columns={\n",
    "        'SHORTNAME': 'TYPENAME',\n",
    "        'DESC': 'TYPELONGNAME',\n",
    "        'ID': 'HOUSETYPE'\n",
    "    }\n",
    ")\n",
    "housta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:09:36.286446Z",
     "start_time": "2021-12-10T12:09:35.628089Z"
    },
    "id": "dr8i2Q7pyfka"
   },
   "outputs": [],
   "source": [
    "hous = hous.merge(\n",
    "    houst[[\n",
    "        'HOUSETYPE', 'TYPENAME', 'TYPELONGNAME'\n",
    "    ]].drop_duplicates(),\n",
    "    on='HOUSETYPE'\n",
    ")\n",
    "if 'HOUSETYPE1' in hous.columns:\n",
    "    hous = hous.merge(\n",
    "        housta[[\n",
    "            'HOUSETYPE', 'TYPENAME', 'TYPELONGNAME'\n",
    "        ]].rename(\n",
    "            columns={\n",
    "                'HOUSETYPE': 'HOUSETYPE1'\n",
    "            }\n",
    "        ).drop_duplicates(),\n",
    "        on='HOUSETYPE1',\n",
    "        how='left',\n",
    "        suffixes=(None, '1')\n",
    "    )\n",
    "else:\n",
    "    hous['HOUSETYPE1'] = np.nan\n",
    "    hous['TYPELONGNAME1'] = np.nan\n",
    "    hous['HOUSENUM1'] = np.nan\n",
    "    hous['TYPENAME1'] = np.nan\n",
    "if 'HOUSETYPE2' in hous.columns:\n",
    "    hous = hous.merge(\n",
    "        housta[[\n",
    "            'HOUSETYPE', 'TYPENAME', 'TYPELONGNAME'\n",
    "        ]].rename(\n",
    "            columns={\n",
    "                'HOUSETYPE': 'HOUSETYPE2'\n",
    "            }\n",
    "        ).drop_duplicates(),\n",
    "        on='HOUSETYPE2',\n",
    "        how='left',\n",
    "        suffixes=(None, '2')\n",
    "    )\n",
    "else:\n",
    "    hous['HOUSETYPE2'] = np.nan\n",
    "    hous['TYPELONGNAME2'] = np.nan\n",
    "    hous['HOUSENUM2'] = np.nan\n",
    "    hous['TYPENAME2'] = np.nan\n",
    "cleanup(houst)\n",
    "cleanup(housta)\n",
    "hous.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:10:23.997654Z",
     "start_time": "2021-12-10T12:09:36.288408Z"
    },
    "id": "AcMDH6obyjWJ"
   },
   "outputs": [],
   "source": [
    "hous['LEVEL'] = '10'\n",
    "hous['LEVELNAME'] = 'Здание/Сооружение'\n",
    "hous['NAME'] = hous[['TYPELONGNAME', 'HOUSENUM']].apply(\n",
    "    lambda x: (\n",
    "        x['TYPELONGNAME'].lower() + ' '\n",
    "        if x['TYPELONGNAME'] == x['TYPELONGNAME']\n",
    "        else ''\n",
    "    ) + x['HOUSENUM'],\n",
    "    axis=1\n",
    ")\n",
    "hous['NAME1'] = hous[['TYPELONGNAME1', 'HOUSENUM1']].apply(\n",
    "    lambda x: (\n",
    "        x['TYPELONGNAME1'].lower() + ' '\n",
    "        if x['TYPELONGNAME1'] == x['TYPELONGNAME1']\n",
    "        else ''\n",
    "    ) + (\n",
    "        x['HOUSENUM1']\n",
    "        if x['HOUSENUM1'] == x['HOUSENUM1']\n",
    "        else ''\n",
    "    ),\n",
    "    axis=1\n",
    ")\n",
    "hous['NAME2'] = hous[['TYPELONGNAME2', 'HOUSENUM2']].apply(\n",
    "    lambda x: (\n",
    "        x['TYPELONGNAME2'].lower() + ' '\n",
    "        if x['TYPELONGNAME2'] == x['TYPELONGNAME2']\n",
    "        else ''\n",
    "    ) + (\n",
    "        x['HOUSENUM2']\n",
    "        if x['HOUSENUM2'] == x['HOUSENUM2']\n",
    "        else ''\n",
    "    ),\n",
    "    axis=1\n",
    ")\n",
    "hous.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:10:51.907897Z",
     "start_time": "2021-12-10T12:10:23.999280Z"
    },
    "id": "AvFeFQzPt-Hb"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob(os.path.join(region, 'AS_HOUSES_PARAMS_*.XML'))\n",
    "fname = fname[0]\n",
    "hp = parse_xml(fname)\n",
    "hp = hp[\n",
    "    (\n",
    "        hp['TYPEID'] == '7'\n",
    "    ) & (\n",
    "        hp['CHANGEIDEND'] == '0'\n",
    "    )\n",
    "]\n",
    "hp = hp[\n",
    "    hp.ENDDATE.apply(\n",
    "        lambda x: datetime.datetime.strptime(\n",
    "            x, '%Y-%m-%d'\n",
    "        ) > datetime.datetime.fromtimestamp(\n",
    "            time.time()\n",
    "        )\n",
    "    )\n",
    "]\n",
    "hp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:11:20.908730Z",
     "start_time": "2021-12-10T12:10:51.909177Z"
    },
    "id": "rnN2zZDnt-Hb"
   },
   "outputs": [],
   "source": [
    "hp = hp[['OBJECTID', 'VALUE']].rename(\n",
    "    columns={'VALUE': 'OKTMO'}\n",
    ").drop_duplicates().groupby(\n",
    "    by='OBJECTID'\n",
    ").agg(\n",
    "    lambda x: x.to_list()\n",
    ").to_dict('index')\n",
    "hp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vbiHmc6it-Hc"
   },
   "source": [
    "**Let's concat our address objects with house objects.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:11:21.931044Z",
     "start_time": "2021-12-10T12:11:20.910283Z"
    },
    "id": "eTVM5amLym4Q"
   },
   "outputs": [],
   "source": [
    "hadobj = pd.concat(\n",
    "  [\n",
    "      adobj[[\n",
    "          'OBJECTID', 'OBJECTGUID', 'NAME', 'TYPENAME', 'LEVEL',\n",
    "          'ISACTUAL', 'ISACTIVE', 'TYPELONGNAME', 'LEVELNAME'\n",
    "      ]],\n",
    "      hous[[\n",
    "          'OBJECTID', 'OBJECTGUID', 'HOUSENUM', 'HOUSETYPE',\n",
    "          'TYPENAME', 'TYPELONGNAME', 'HOUSENUM1', 'HOUSETYPE1',\n",
    "          'TYPENAME1', 'TYPELONGNAME1', 'HOUSENUM2', 'HOUSETYPE2',\n",
    "          'TYPENAME2', 'TYPELONGNAME2', 'ISACTUAL', 'ISACTIVE',\n",
    "          'LEVEL', 'NAME', 'NAME1', 'NAME2', 'LEVELNAME'\n",
    "      ]]\n",
    "  ],\n",
    "  sort=True,\n",
    "  ignore_index=True\n",
    ")\n",
    "cleanup(adobj)\n",
    "cleanup(hous)\n",
    "hadobj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6KXFUVpiy0Ri"
   },
   "source": [
    "## Read Administrative Relations File"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bueulg6vt-Hc"
   },
   "source": [
    "**More exciting part is coming, I guess. The important thing is whether `PATH` column is included or not. Cause if it isn't, we suffer in attempts to restore paths ourselves.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:13:23.288989Z",
     "start_time": "2021-12-10T12:11:21.932175Z"
    },
    "id": "WeL_VySbyqDQ"
   },
   "outputs": [],
   "source": [
    "fname = glob.glob(os.path.join(region, 'AS_MUN_HIERARCHY_*.XML'))\n",
    "if len(fname) != 1:\n",
    "    msg = f'Please check file count for region {region} there are {len(fname)} files'\n",
    "    logger.error(msg)\n",
    "    raise Exception(msg)\n",
    "fname = fname[0]\n",
    "adm = parse_xml(fname)\n",
    "adm = adm[\n",
    "    adm.ENDDATE.apply(\n",
    "        lambda x: datetime.datetime.strptime(\n",
    "            x, '%Y-%m-%d'\n",
    "        ) > datetime.datetime.fromtimestamp(\n",
    "            time.time()\n",
    "        )\n",
    "    )\n",
    "]\n",
    "chready = 'PATH' in adm.columns\n",
    "cols = ['OBJECTID', 'PARENTOBJID'] + (['PATH'] if chready else [])\n",
    "adm0 = adm[adm['ISACTIVE'] == '1'][cols].merge(\n",
    "    hadobj[(hadobj['ISACTUAL'] == '1') & (hadobj['ISACTIVE'] == '1')],\n",
    "    on='OBJECTID'\n",
    ")\n",
    "cleanup(adm)\n",
    "adm0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:13:23.294048Z",
     "start_time": "2021-12-10T12:13:23.290597Z"
    },
    "id": "AY5pZg4Wt-Hd"
   },
   "outputs": [],
   "source": [
    "chready"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mEmsQtcvz8Ok"
   },
   "source": [
    "## Building Address Chains"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Qg1-kDpSt-Hd"
   },
   "source": [
    "**Option 1: PATHs included, just split them up nicely starting from house objects (level 10).**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:13:23.308619Z",
     "start_time": "2021-12-10T12:13:23.295549Z"
    },
    "id": "VINFW7Fat-Hd"
   },
   "outputs": [],
   "source": [
    "if chready:\n",
    "    chains = [\n",
    "        tuple(y for y in reversed(x.split('.')))\n",
    "        for x in tqdm(adm0[adm0['LEVEL'] == '10']['PATH'])\n",
    "    ]\n",
    "    cleanup(adm0)\n",
    "    print(len(chains), chains[:5])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T13:58:10.263139Z",
     "start_time": "2021-12-10T13:58:10.257887Z"
    },
    "id": "DJ7SRuxFt-Hd"
   },
   "source": [
    "**Option 2: Start the painful process of assembling address chains recursively.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:19:32.083834Z",
     "start_time": "2021-12-10T12:19:04.348487Z"
    },
    "id": "6r3DX7_nzzss"
   },
   "outputs": [],
   "source": [
    "if not chready:\n",
    "    # get child-parent dictionary\n",
    "    rdadm = get_adms(adm0)\n",
    "    rdadm = {k: list(set(v)) for k, v in rdadm.items()}\n",
    "    cleanup(adm0)\n",
    "    print([(k, v) for k, v in rdadm.items()][:5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:27:57.679065Z",
     "start_time": "2021-12-10T12:27:50.710617Z"
    },
    "id": "D5-HjyLW0Cuz"
   },
   "outputs": [],
   "source": [
    "hadobjd = hadobj.set_index('OBJECTID').to_dict('index')\n",
    "if not chready:\n",
    "    # building chains recursively\n",
    "    chains = [\n",
    "        get_adms_rec_rev([x], rdadm, hp, adobjp)\n",
    "        for x in tqdm(hadobj[hadobj['LEVEL'] == '10']['OBJECTID'].drop_duplicates())\n",
    "    ]\n",
    "    chains = [x for x in chains if x is not None]\n",
    "# save and clean\n",
    "hadobj.to_csv(f'{region}_hadobj.csv', index=False)\n",
    "cleanup(hadobj)\n",
    "[(k, v) for k, v in hadobjd.items()][:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FCy9SGVBt-He"
   },
   "source": [
    "**Sometimes there are multiple chains for one object. So let's try to fix that.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:33:57.072486Z",
     "start_time": "2021-12-10T12:33:55.997559Z"
    },
    "id": "vLmRu58T0EWR"
   },
   "outputs": [],
   "source": [
    "dfch = pd.DataFrame()\n",
    "if not chready:\n",
    "    odd_chains = [\n",
    "        tuple(x)\n",
    "        for x in chains\n",
    "        if type(x[0]) == list\n",
    "    ]\n",
    "    odd_chains = [reduce_included(x) for x in odd_chains]\n",
    "    odd_chains = [x for x, y in odd_chains if y] + [z for x, y in odd_chains if not y for z in x]\n",
    "    chains = [\n",
    "        tuple(x)\n",
    "        for x in chains\n",
    "        if type(x[0]) != list\n",
    "    ] + odd_chains\n",
    "\n",
    "dfch['chain'] = list(set(chains))\n",
    "cleanup(chains)\n",
    "dfch.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "MZacCoOot-He"
   },
   "source": [
    "**Time to process chains in a fashionable manner.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:00.119256Z",
     "start_time": "2021-12-10T12:33:57.073896Z"
    },
    "id": "0fDB2gVGGNqa"
   },
   "outputs": [],
   "source": [
    "dfch['levchain'] = [\n",
    "    tuple([hadobjd[y]['LEVEL'] for y in x if y != '0' and y in hadobjd])\n",
    "    for x in tqdm(dfch['chain'])\n",
    "]\n",
    "dat = [\n",
    "    {\n",
    "        m: l\n",
    "        for m, l in zip(x, y)\n",
    "    }\n",
    "    for x, y in zip(dfch['levchain'], dfch['chain'])\n",
    "]\n",
    "for i in range(10, 0, -1):\n",
    "    dfch[f'{i}'] = [\n",
    "        d[f'{i}']\n",
    "        if f'{i}' in d\n",
    "        else None\n",
    "        for d in dat\n",
    "    ]\n",
    "dfch.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "lQ8OUCT7t-Hf"
   },
   "source": [
    "**Now we'll attempt to structure all those chains, so that they fit into old-fashioned way of saying the address.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:01.298227Z",
     "start_time": "2021-12-10T12:34:01.023881Z"
    },
    "id": "otcnWqPdGQ4T"
   },
   "outputs": [],
   "source": [
    "chl = list(set(dfch['levchain'].apply(lambda x: '-'.join(x))))\n",
    "df = pd.DataFrame()\n",
    "df['levchain'] = chl\n",
    "for i in range(10, 0, -1):\n",
    "    dat = [(f'{i}' in y.split('-')) * 1 for y in chl]\n",
    "    df[f'{i}'] = dat\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:01.757834Z",
     "start_time": "2021-12-10T12:34:01.729905Z"
    },
    "id": "Uy6hKMVOGUPJ"
   },
   "outputs": [],
   "source": [
    "lst = df.apply(get_town, axis=1)\n",
    "df['street'] = [x[0] for x in lst]\n",
    "df['town'] = [x[1] for x in lst]\n",
    "df['leftover'] = [x[2] for x in lst]\n",
    "df['muni'] = [x[3] for x in lst]\n",
    "df['levchain'] = df['levchain'].apply(lambda x: tuple(x.split('-')))\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mEHeM7-dt-Hf"
   },
   "source": [
    "**Now join chains and their structure together.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:02.677977Z",
     "start_time": "2021-12-10T12:34:02.372896Z"
    },
    "id": "oDTRvlqhGXEj"
   },
   "outputs": [],
   "source": [
    "dfch = dfch.merge(df[['levchain', 'street', 'town', 'leftover', 'muni']], on='levchain')\n",
    "dfch['region'] = region\n",
    "dfch.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7tXHpsloGlhh"
   },
   "source": [
    "## Save"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SCewdcQJt-Hg"
   },
   "source": [
    "**Should be self-explanatory**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:05.936769Z",
     "start_time": "2021-12-10T12:34:04.105182Z"
    },
    "id": "ySj71rvjGaip"
   },
   "outputs": [],
   "source": [
    "dfch.to_csv(f'{region}_parsed_chains.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "gZ-MflHpHFj0"
   },
   "source": [
    "# Read Result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_wzUeDyBt-Hg"
   },
   "source": [
    "**This part is __somewhat__ independent, meaning you can restart the runtime after successfully running all the previous cells.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:02:41.209612Z",
     "start_time": "2021-12-10T12:02:41.130859Z"
    },
    "id": "zIfVLYlBGqAH"
   },
   "outputs": [],
   "source": [
    "# replace with your path\n",
    "from google.colab import drive\n",
    "drive.mount(\"/content/drive\", force_remount=True)\n",
    "%cd \"drive/MyDrive/gar_colab/garbdfias\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:09.872137Z",
     "start_time": "2021-12-10T12:34:09.868320Z"
    },
    "id": "WC-fygmJHIHK"
   },
   "outputs": [],
   "source": [
    "import glob\n",
    "import random\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from ast import literal_eval\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:10.223479Z",
     "start_time": "2021-12-10T12:34:10.217531Z"
    },
    "id": "m9fZqgYxHLFT"
   },
   "outputs": [],
   "source": [
    "regions = glob.glob('data/[0-9][0-9]')\n",
    "region = regions[0]\n",
    "region"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:11.399403Z",
     "start_time": "2021-12-10T12:34:10.666080Z"
    },
    "id": "dwuJP6WrHNoL"
   },
   "outputs": [],
   "source": [
    "hadobj = pd.read_csv(f'{region}_hadobj.csv')\n",
    "hadobj.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FnNK8UbSt-Hh"
   },
   "source": [
    "**Just some useful utilities for a dataframe. Notice how some columns are not of type 'string' anymore, so we'll have to fix that.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:11.525223Z",
     "start_time": "2021-12-10T12:34:11.402217Z"
    },
    "id": "CpduY0zlHSs8"
   },
   "outputs": [],
   "source": [
    "hadobj.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:11.691570Z",
     "start_time": "2021-12-10T12:34:11.618612Z"
    },
    "id": "v3ltio4THTU9"
   },
   "outputs": [],
   "source": [
    "hadobj.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:13.054065Z",
     "start_time": "2021-12-10T12:34:12.175748Z"
    },
    "id": "2yKjXrdXHjZ7"
   },
   "outputs": [],
   "source": [
    "for x in [\n",
    "          'OBJECTID', 'HOUSENUM', 'HOUSENUM1',\n",
    "          'HOUSENUM2', 'LEVEL', 'NAME',\n",
    "          'TYPELONGNAME', 'TYPELONGNAME1', 'TYPELONGNAME2',\n",
    "          'TYPENAME', 'TYPENAME1', 'TYPENAME2']:\n",
    "    hadobj[x] = hadobj[x].apply(lambda y: str(y) if y == y else np.nan)\n",
    "hadobj['OBJECTID'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:13.538615Z",
     "start_time": "2021-12-10T12:34:13.056040Z"
    },
    "id": "9IZEpABlHoeU"
   },
   "outputs": [],
   "source": [
    "dfch = pd.read_csv(f'{region}_parsed_chains.csv')\n",
    "dfch.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:27.173960Z",
     "start_time": "2021-12-10T12:34:13.540037Z"
    },
    "id": "r0dkdX12Hsp6",
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for x in ['chain', 'levchain', 'leftover', 'muni']:\n",
    "    dfch[x] = [literal_eval(y) for y in tqdm(dfch[x])]\n",
    "for x in range(1, 11, 1):\n",
    "    dfch[f'{x}'] = dfch[f'{x}'].astype(str).apply(lambda y: y.split('.')[0] if y == y else y)\n",
    "    dfch[f'{x}'] = dfch[f'{x}'].apply(lambda y: y if y != '' else np.nan)\n",
    "for x in ['street', 'town', 'region']:\n",
    "    dfch[x] = dfch[x].astype(str).apply(lambda y: y.split('.')[0] if y == y else y)\n",
    "    dfch[x] = dfch[x].apply(lambda y: y if y != '' else np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:27.196802Z",
     "start_time": "2021-12-10T12:34:27.176144Z"
    },
    "id": "kiqT9WpjHv--"
   },
   "outputs": [],
   "source": [
    "dfch.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:30.019337Z",
     "start_time": "2021-12-10T12:34:27.197989Z"
    },
    "id": "sH3IfipKHy2a"
   },
   "outputs": [],
   "source": [
    "hadobjd = hadobj.set_index('OBJECTID').to_dict('index')\n",
    "[(k, v) for k, v in hadobjd.items()][:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aJfU_xFUH7Ms"
   },
   "source": [
    "## Plaintext Addresses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6JyZ8IRHt-Hi"
   },
   "source": [
    "**Let's just conctenate object names. It's not a good idea sometimes, just keep that in mind.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:32.881118Z",
     "start_time": "2021-12-10T12:34:30.020770Z"
    },
    "id": "KF5ZinS7H22n"
   },
   "outputs": [],
   "source": [
    "addresses = {x[0]: [] for x in tqdm(dfch.chain)}\n",
    "for x in tqdm(dfch.chain):\n",
    "    for y in x:\n",
    "        if y != '0':\n",
    "            tpln = hadobjd[y]['TYPELONGNAME']\n",
    "            tpln1 = hadobjd[y]['TYPELONGNAME1']\n",
    "            tpln2 = hadobjd[y]['TYPELONGNAME2']\n",
    "            if tpln == tpln:\n",
    "                addresses[x[0]].append(tpln)\n",
    "            if hadobjd[y]['LEVEL'] != '10':\n",
    "                addresses[x[0]].append(hadobjd[y]['NAME'])\n",
    "            else:\n",
    "                addresses[x[0]].append(hadobjd[y]['HOUSENUM'])\n",
    "                if hadobjd[y]['HOUSENUM1'] == hadobjd[y]['HOUSENUM1']:\n",
    "                    if tpln1 == tpln1:\n",
    "                        addresses[x[0]].append(tpln1)\n",
    "                    addresses[x[0]].append(hadobjd[y]['HOUSENUM1'])\n",
    "                    if hadobjd[y]['HOUSENUM2'] == hadobjd[y]['HOUSENUM2']:\n",
    "                        if tpln2 == tpln2:\n",
    "                            addresses[x[0]].append(tpln2)\n",
    "                        addresses[x[0]].append(hadobjd[y]['HOUSENUM2'])\n",
    "addresses = {\n",
    "    k: ' '.join(v)\n",
    "    for k, v in addresses.items()\n",
    "}\n",
    "sample = random.sample(addresses.keys(), 10)\n",
    "{s: addresses[s].lower() for s in sample}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Jz0JEPKjIFUP"
   },
   "source": [
    "## Structured Addresses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "n1k_NZWHt-Hi"
   },
   "source": [
    "**Let's give our addresses more familiar structure and look at some confusing examples.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:32.894522Z",
     "start_time": "2021-12-10T12:34:32.883441Z"
    },
    "id": "4lloG-ewH-z-"
   },
   "outputs": [],
   "source": [
    "def get_struct_addr(x):\n",
    "    if x['town'] != 'nan' and x[x['town']] in hadobjd:\n",
    "        town = hadobjd[x[x['town']]]\n",
    "        town = town['TYPELONGNAME'] + ' ' + town['NAME']\n",
    "    else:\n",
    "        town = ''\n",
    "    if x['street'] != 'nan' and x[x['street']] in hadobjd:\n",
    "        street = hadobjd[x[x['street']]]\n",
    "        street = street['TYPELONGNAME'] + ' ' + street['NAME']\n",
    "    else:\n",
    "        street = ''\n",
    "    house = hadobjd[x['10']]\n",
    "    tpln = house['TYPELONGNAME'] + ' ' if house['TYPELONGNAME'] == house['TYPELONGNAME'] else ''\n",
    "    tpln1 = house['TYPELONGNAME1'] + ' ' if house['TYPELONGNAME1'] == house['TYPELONGNAME1'] else ''\n",
    "    tpln2 = house['TYPELONGNAME2'] + ' ' if house['TYPELONGNAME2'] == house['TYPELONGNAME2'] else ''\n",
    "    house0 = tpln + house['HOUSENUM']\n",
    "    house1 = ''\n",
    "    house2 = ''\n",
    "    \n",
    "    if house['HOUSENUM1'] == house['HOUSENUM1']:\n",
    "        house1 = tpln1 + house['HOUSENUM1']\n",
    "    if house['HOUSENUM2'] == house['HOUSENUM2']:\n",
    "        house2 = tpln2 + house['HOUSENUM2']\n",
    "    leftover = []\n",
    "    for y in x['leftover']:\n",
    "        leftover.append(hadobjd[x[y]]['TYPELONGNAME'])\n",
    "        leftover.append(hadobjd[x[y]]['NAME'])\n",
    "    leftover = ' '.join(leftover)\n",
    "    muni = []\n",
    "    for y in x['muni']:\n",
    "        muni.append(hadobjd[x[y]]['TYPELONGNAME'])\n",
    "        muni.append(hadobjd[x[y]]['NAME'])\n",
    "    muni = ' '.join(muni)\n",
    "    return {\n",
    "        'town': town,\n",
    "        'street': street,\n",
    "        'house': house0,\n",
    "        'house1': house1,\n",
    "        'house2': house2,\n",
    "        'leftover': leftover,\n",
    "        'muni': muni\n",
    "    }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:34:32.932170Z",
     "start_time": "2021-12-10T12:34:32.896413Z"
    },
    "id": "oHiLLyOIt-Hj"
   },
   "outputs": [],
   "source": [
    "dfch.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:35:29.321395Z",
     "start_time": "2021-12-10T12:34:32.933662Z"
    },
    "id": "KBxdLGYFIJVc"
   },
   "outputs": [],
   "source": [
    "struct_addresses = dfch.apply(get_struct_addr, axis=1)\n",
    "struct_addresses[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:35:29.946318Z",
     "start_time": "2021-12-10T12:35:29.322583Z"
    },
    "id": "JAjIo1-FILhk"
   },
   "outputs": [],
   "source": [
    "structdf = pd.DataFrame.from_records(struct_addresses)\n",
    "structdf.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:36:16.920603Z",
     "start_time": "2021-12-10T12:35:29.947506Z"
    },
    "id": "fe-hbxw3IQro"
   },
   "outputs": [],
   "source": [
    "grouped = structdf.groupby(by=['town', 'street', 'house', 'house1', 'house2']).aggregate(lambda x: x.to_list())\n",
    "grouped.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-12-10T12:36:17.022799Z",
     "start_time": "2021-12-10T12:36:16.921745Z"
    },
    "id": "p2gBGA3PIVHV"
   },
   "outputs": [],
   "source": [
    "grouped[(grouped['muni'].apply(lambda x: len(x) > 1))].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "u4DJQCsEt-Hj"
   },
   "outputs": [],
   "source": [
    ""
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "GAR16.ipynb",
   "private_outputs": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
